﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DataAccessLayer
{
    public static class ContractDB
    {

        public static List<Contract> GetContracts()
        {
            List<Contract> myContracts = new List<Contract>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT con.Id, con.StartDate, con.EndDate, con.CompanyId, com.Name as ComName, con.ContractFormulaId, con.UserId From Contract con, Company com WHERE con.CompanyId = com.Id";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Contract contract = new Contract();
                    contract.Id = (int)reader["Id"];
                    contract.StartDate = (DateTime)reader["StartDate"];
                    contract.EndDate = (DateTime)reader["EndDate"];
                    contract.CompanyId = (int)reader["CompanyId"];
                    contract.ContractFormulaId = (int)reader["ContractFormulaId"];
                    contract.UserId = reader["UserId"].ToString();
                    contract.CompanyName = reader["ComName"].ToString();
                    myContracts.Add(contract);
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return myContracts;
        }

        public static List<Contract> GetContractsLopendUser(string userid)
        {
            List<Contract> myContracts = new List<Contract>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@userid", SqlDbType.VarChar);
            cmd.Parameters["@userid"].Value = userid;
            cmd.CommandText = "SELECT con.Id, con.StartDate, con.EndDate, con.CompanyId, com.Name as ComName, con.ContractFormulaId, con.UserId, conf.Description From Contract con, Company com, ContractFormula conf WHERE con.CompanyId = com.Id AND con.ContractFormulaId = conf.Id AND UserId = @userid AND con.EndDate >= Convert(datetime, Convert(int, GetDate()))";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Contract contract = new Contract();
                    contract.Id = (int)reader["Id"];
                    contract.StartDate = (DateTime)reader["StartDate"];
                    contract.EndDate = (DateTime)reader["EndDate"];
                    contract.CompanyId = (int)reader["CompanyId"];
                    contract.ContractFormulaId = (int)reader["ContractFormulaId"];
                    contract.UserId = reader["UserId"].ToString();
                    contract.CompanyName = reader["ComName"].ToString();
                    contract.Description = reader["Description"].ToString();
                    myContracts.Add(contract);
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return myContracts;
        }

        public static List<Contract> GetContractsLopend()
        {
            List<Contract> Contracts = new List<Contract>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT con.Id, con.StartDate, con.EndDate, con.CompanyId, com.Name as ComName, con.ContractFormulaId, conf.Description From Contract con, Company com, ContractFormula conf WHERE con.CompanyId = com.Id AND con.ContractFormulaId = conf.Id AND con.EndDate >= Convert(datetime, Convert(int, GetDate()))";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Contract contract = new Contract();
                    contract.Id = (int)reader["Id"];
                    contract.StartDate = (DateTime)reader["StartDate"];
                    contract.EndDate = (DateTime)reader["EndDate"];
                    contract.CompanyId = (int)reader["CompanyId"];
                    contract.ContractFormulaId = (int)reader["ContractFormulaId"];
                    contract.CompanyName = reader["ComName"].ToString();
                    contract.Description = reader["Description"].ToString();
                    Contracts.Add(contract);
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return Contracts;
        }

        public static List<Contract> GetContractsAfgelopenUser(string userid)
        {
            List<Contract> myContracts = new List<Contract>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@userid", SqlDbType.VarChar);
            cmd.Parameters["@userid"].Value = userid;
            cmd.CommandText = "SELECT con.Id, con.StartDate, con.EndDate, con.CompanyId, com.Name as ComName, con.ContractFormulaId, con.UserId, conf.Description From Contract con, Company com, ContractFormula conf WHERE con.CompanyId = com.Id AND con.ContractFormulaId = conf.Id AND UserId = @userid AND con.EndDate < Convert(datetime, Convert(int, GetDate()))";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Contract contract = new Contract();
                    contract.Id = (int)reader["Id"];
                    contract.StartDate = (DateTime)reader["StartDate"];
                    contract.EndDate = (DateTime)reader["EndDate"];
                    contract.CompanyId = (int)reader["CompanyId"];
                    contract.ContractFormulaId = (int)reader["ContractFormulaId"];
                    contract.UserId = reader["UserId"].ToString();
                    contract.CompanyName = reader["ComName"].ToString();
                    contract.Description = reader["Description"].ToString();
                    myContracts.Add(contract);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return myContracts;
        }

        public static List<Contract> GetContractsAfgelopen()
        {
            List<Contract> Contracts = new List<Contract>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT con.Id, con.StartDate, con.EndDate, con.CompanyId, com.Name as ComName, con.ContractFormulaId, conf.Description From Contract con, Company com, ContractFormula conf WHERE con.CompanyId = com.Id AND con.ContractFormulaId = conf.Id AND con.EndDate < Convert(datetime, Convert(int, GetDate()))";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Contract contract = new Contract();
                    contract.Id = (int)reader["Id"];
                    contract.StartDate = (DateTime)reader["StartDate"];
                    contract.EndDate = (DateTime)reader["EndDate"];
                    contract.CompanyId = (int)reader["CompanyId"];
                    contract.ContractFormulaId = (int)reader["ContractFormulaId"];
                    contract.CompanyName = reader["ComName"].ToString();
                    contract.Description = reader["Description"].ToString();
                    Contracts.Add(contract);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return Contracts;
        }

        public static int StopContract(Contract c)
        {
            int aantal;
            DateTime createDate = DateTime.Now;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "UPDATE Contract SET EndDate = Convert(datetime, Convert(int, GetDate()) - 1) WHERE Id=@ID;";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@ID", c.Id);

                //query uitvoeren
                aantal = command.ExecuteNonQuery() ;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int InsertContract(Contract c)
        {

            int aantal;
            DateTime createDate = DateTime.Now;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "Insert into Contract (StartDate, EndDate, CompanyId, ContractFormulaId) VALUES (@StartDate, @EndDate, @CompanyId, @ContractFormulaId)";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@StartDate", c.StartDate);
                command.Parameters.AddWithValue("@EndDate", c.EndDate);
                command.Parameters.AddWithValue("@CompanyId", c.CompanyId);
                command.Parameters.AddWithValue("@ContractFormulaId", c.ContractFormulaId);
                

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int UpdateContract(Contract c)
        {

            int aantal;
            DateTime createDate = DateTime.Now;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "Update  Contract SET StartDate=@Startdate, EndDate=@EndDate, CompanyId=@CompanyId, ContractFormulaId=@ContractFormulaId WHERE Id=@Id";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@Id", c.Id);
                command.Parameters.AddWithValue("@StartDate", c.StartDate);
                command.Parameters.AddWithValue("@EndDate", c.EndDate);
                command.Parameters.AddWithValue("@CompanyId", c.CompanyId);
                command.Parameters.AddWithValue("@ContractFormulaId", c.ContractFormulaId);


                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int DeleteContract(Contract c)
        {

            int aantal;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "DELETE FROM Contract WHERE Id = @Contract_ID;";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query               
                command.Parameters.AddWithValue("@Contract_ID", c.Id);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }
            catch (NullReferenceException ex)
            {
                return aantal = 0;
            }

            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static bool ContractControle(int CompanyId, DateTime startDate, DateTime endDate)
        {
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@startDate", SqlDbType.DateTime);
            cmd.Parameters["@startDate"].Value = startDate;
            cmd.Parameters.Add("@endDate", SqlDbType.DateTime);
            cmd.Parameters["@endDate"].Value = endDate;
            cmd.Parameters.Add("@CompanyId", SqlDbType.Int);
            cmd.Parameters["@CompanyId"].Value = CompanyId;
            cmd.CommandText = "SELECT count(c.Id) From Contract c WHERE @CompanyId = c.CompanyId AND @startDate >= StartDate AND @endDate <= EndDate ";
            cmd.Connection = con;

            try
            {
                con.Open();
                int rows = (int)cmd.ExecuteScalar();
                if (rows >= 1)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static bool ContractReservationControle(int CompanyId)
        {
            DateTime now = DateTime.Now;
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@now", SqlDbType.DateTime);
            cmd.Parameters["@now"].Value = now;
            cmd.Parameters.Add("@CompanyId", SqlDbType.Int);
            cmd.Parameters["@CompanyId"].Value = CompanyId;
            cmd.CommandText = "SELECT count(r.Id) From Reservation r, Contract c WHERE @CompanyId = r.CompanyId AND c.EndDate >= @now AND r.EndDate >= @now";
            cmd.Connection = con;

            try
            {
                con.Open();
                int rows = (int)cmd.ExecuteScalar();
                if (rows >= 1)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
